<?php
class Model_Category extends CI_Model
{
	

	
	function get_specific_all($num, $offset, $oneone, $keyarray, $language_id)
	{
		$this->db->join('category_description', 'category_description.category_id = category.category_id');
		
		
		$this->db->where('category_description.language_id', $language_id);
		if($oneone)
		{
			$this->db->order_by($oneone);
		}
		else
		{
			$this->db->order_by('category.lft', 'ASC');
		}
		
		return $this->db->get('category', $num, $offset);
		
	}
	
	/*
	获取特定条件的记录总数
	*/
	
	function get_specific_count_all($keyarray)
	{
		$this->db->from('category');
		$this->db->join('category_description', 'category_description.category_id = category.category_id');
		
		$this->db->where('category_description.language_id',3);
		
		return $this->db->count_all_results();
	}
	
	function get_fullpath_name($category_id, $seperator)
	{
		$this->db->from('category c0');	
		$this->db->from('category c');	
		$this->db->join('category_description cd', 'cd.category_id = c.category_id');
		$this->db->where('cd.language_id', '3');
		$this->db->where('c0.category_id', (int)$category_id);
		$this->db->where('c.lft <= c0.lft');
		$this->db->where('c.rgt >= c0.rgt');
		$query = $this->db->get();
		
		$name = '';
		
		foreach($query->result_array() as $i => $result)
		{
			if($i == 0)
			{
				$name .= $result['name'];
			}
			else
			{
				$name .= $seperator.$result['name'];
			}
		}
		
		return $name;
		
		
	}
	
	
	

	
	function getCategoryDescriptions($category_id) {
		$category_description_data = array();
		
		
		$this->db->from('category_description');
		$this->db->where('category_id', (int)$category_id);
		$query = $this->db->get();
		foreach ($query->result() as $result) {
			$category_description_data[$result->language_id] = array(
				'name'             => $result->name,
				'meta_keyword'     => $result->meta_keyword,
				'meta_description' => $result->meta_description,
				'description'      => $result->description,
			);
		}
		
		return $category_description_data;
	}	
	
	
	function getCategories($parent_id) {
		
	
		
			$category_data = array();
		
			$language_id = $this->Model_Config->getLanguageID($this->Model_Config->getKey('config_admin_language'));
			
			$this->db->from('category c');
			$this->db->join('category_description cd', 'c.category_id = cd.category_id');
			$this->db->where('c.parent_id', $parent_id);
			$this->db->where('cd.language_id', $language_id);
			$this->db->order_by('c.sort_order', 'ASC');
			$this->db->order_by('cd.name', 'ASC');
			$query = $this->db->get();
		
			foreach ($query->result() as $result) {
				$category_data[] = array(
					'category_id' => $result->category_id,
					'name'        => $this->getPath($result->category_id, $language_id),
					'status'  	  => $result->status,
					'sort_order'  => $result->sort_order, 
				);
			
				$category_data = array_merge($category_data, $this->getCategories($result->category_id));
			}	
	
			
		
		
		return $category_data;
	}
	
	public function getPath($category_id) {

			$language_id = $this->Model_Config->getLanguageID($this->Model_Config->getKey('config_admin_language'));
			
			$this->db->from('category c');
			$this->db->join('category_description cd', 'c.category_id = cd.category_id');
			$this->db->where('c.category_id', (int)$category_id);
			$this->db->where('cd.language_id', (int)$language_id);
			$this->db->order_by('c.sort_order', 'ASC');
			$this->db->order_by('cd.name', 'ASC');
			$query = $this->db->get();
		
		
		$category_info = $query->row();
		
		if ($category_info->parent_id) {
			return $this->getPath($category_info->parent_id, $language_id) . $this->lang->line('text_separator') . $category_info->name;
		} else {
			return $category_info->name;
		}
	}
	
	function getCategoryStores($category_id) {
		$category_store_data = array();
		
		
		$this->db->from('category_to_store');
		$this->db->where('category_id', (int)$category_id);
		$query = $this->db->get();	

		foreach ($query->result() as $result) {
			$category_store_data[] = $result->store_id;
		}
		
		return $category_store_data;
	}
	
	function getCategoryLayouts($category_id) {
		$category_layout_data = array();
		
		
		$this->db->from('category_to_layout');
		$this->db->where('category_id', (int)$category_id);
		$query = $this->db->get();	
		
		foreach ($query->result() as $result) {
			$category_layout_data[$result['store_id']] = $result['layout_id'];
		}
		
		return $category_layout_data;
	}
	
	function getCategory($category_id) {
		$query = $this->db->query("SELECT DISTINCT *, (SELECT keyword FROM url_alias WHERE query = 'category_id=" . (int)$category_id . "') AS keyword FROM category WHERE category_id = '" . (int)$category_id . "'");
		
		
		
		return $query->row_array();
	} 
	
	function addCategory($data) {
		$basic_data = array(
			"parent_id"	=>	(int)$data['parent_id'],
			"top"	=>	(isset($data['top']) ? (int)$data['top'] : 0),
			"column"	=>	(int)$data['column'] ,
			"sort_order"	=>	(int)$data['sort_order'],
			"status"	=>	(int)$data['status'],
			"date_modified"	=>	date("Y-m-d H:i:s"),
			"date_added"	=>	date("Y-m-d H:i:s"),
			
		);
		
		
		
		
		
		
		
		$this->db->trans_start();			
			
		$this->db->query("LOCK TABLE " . $this->db->dbprefix . "category WRITE");
		
		$this->db->from('category');
		$this->db->where('parent_id', (int)$basic_data['parent_id']);
		$this->db->where('sort_order <= ', (int)$basic_data['sort_order']);
		$this->db->order_by("sort_order", "DESC");
		$this->db->limit(1);
		$query = $this->db->get();	
		
		//新增位置前有兄弟節點		
		if($query->num_rows()==1)
		{
			$prev_node = $query->row();
			$lft = $prev_node->rgt + 1;
			$depth = $prev_node->depth;
		}
		else
		{				
			$this->db->from('category');
			$this->db->where('category_id', (int)$basic_data['parent_id']);
			$query = $this->db->get();	
			//新增位置有父節點		
			if($query->num_rows()==1)
			{
				$parent_node = $query->row();
				$lft = $parent_node->lft + 1;
				$depth = $parent_node->depth + 1;
			}
			//新增位置為第一個節點
			else	
			{				
				$lft = 1;
				$depth = 0;
			}
		}
		
		//寫入數據庫
		$this->db->set('rgt', 'rgt + 2', FALSE);
		$this->db->where('rgt >= ', (int)$lft);
		$this->db->update('category');
		$this->db->set('lft', 'lft + 2', FALSE);
		$this->db->where('lft >= ', (int)$lft);
		$this->db->update('category');
		
		$basic_data['lft'] = $lft;
		$basic_data['rgt'] = $lft + 1;
		$basic_data['depth'] = $depth;	
			
			
		if($this->db->insert('category',$basic_data))
		{
			$category_id = $this->db->insert_id();
			
			$this->db->query("UNLOCK TABLES");	
			
			if (isset($data['image'])) {
				$image_data = array(
					"image" => $data['image'],
				);
				$this->db->where('category_id', (int)$category_id);
				$this->db->update('category', $image_data);
				
			}
			
		
			foreach($data['category_description'] as $language_id => $value){
				
				$desc_data = array(
					'category_id'=>(int)$category_id,
					'language_id'=>(int)$language_id,
					'name' => $value['name'],
					'meta_keyword'=>$value['meta_keyword'],
					'meta_description'=>$value['meta_description'],
					'description'=>$value['description'],
					
				);
				
				$this->db->insert('category_description',$desc_data);
				
			}
			
			if (isset($data['category_store'])) {
				foreach ($data['category_store'] as $store_id) {
					$category_to_store_data = array(
						"category_id" => (int)$category_id,
						"store_id" => (int)$store_id,
					);
					$this->db->insert('category_to_store', $category_to_store_data);
					
				}
			}

			if (isset($data['category_layout'])) {
				foreach ($data['category_layout'] as $store_id => $layout) {
					if ($layout['layout_id']) {
						$category_layout_data = array(
							"category_id"	=> (int)$category_id,
							"store_id"	=> (int)$store_id,
							"layout_id"	=> (int)$layout['layout_id'],
						);
						$this->db->insert('category_to_layout', $category_layout_data);
						
					}
				}
			}
						
			if ($data['keyword']) {
				$keyword_data = array(
					"query" => 'category_id=' . (int)$category_id ,
					"keyword" => $data['keyword'],
				);
				
				$this->db->insert('url_alias', $keyword_data);
				
			}
		
		
				
					
		}
		else
		{			
			$this->db->query("UNLOCK TABLES");	
		}
		
		/*
		//保存操作记录
		$action_data = array(
			'administrator_id' => (int)$this->session->userdata('administrator_id'),
			'ip_address' => get_ip(),
			'action_type' => 'add',
			'module_id' => strtolower($this->uri->segment(1)),
			'record_id' => $basic_data['category_id'],
			'action_time' => time(),
		);
		
		$success = $this->db->insert('administrators_action', $action_data);
		*/
		
		$this->db->trans_complete();
		
	
	}
	
	function editCategory($category_id, $data) {
		
		$this->db->trans_start();
		$basic_data = array(
			"parent_id" => (int)$data['parent_id'],
			"top" => (isset($data['top']) ? (int)$data['top'] : 0),
			"column" => (int)$data['column'],
			"sort_order" => (int)$data['sort_order'],
			"status" => (int)$data['status'],
			"date_modified" => date("Y-m-d H:i:s"),
		);
		
		$this->db->query("LOCK TABLE " . $this->db->dbprefix . "category WRITE");
		
		//原節點數據
		$this->db->from('category');
		$this->db->where('category_id', (int)$category_id);
		$query = $this->db->get();	
		$from_node = $query->row();
		$from_lft = $from_node->lft;
		$from_rgt = $from_node->rgt;
		$width = $from_rgt - $from_lft + 1;
		
		//新節點數據
		$this->db->from('category');
		$this->db->where('parent_id', (int)$basic_data['parent_id']);
		$this->db->where('category_id !=', (int)$category_id);
		$this->db->where('sort_order <= ', (int)$basic_data['sort_order']);
		$this->db->order_by("sort_order", "DESC");
		$this->db->limit(1);
		$query = $this->db->get();	
		
		//新增位置前有兄弟節點		
		if($query->num_rows()==1)
		{
			$prev_node = $query->row();
			$to_lft = $prev_node->rgt + 1;
			$to_rgt = $to_lft + $width - 1;
			$depth = $prev_node->depth;
		}
		else
		{				
			$this->db->from('category');
			$this->db->where('category_id', (int)$basic_data['parent_id']);
			$query = $this->db->get();	
			//新位置有父節點		
			if($query->num_rows()==1)
			{
				$parent_node = $query->row();
				$to_lft = $parent_node->lft + 1;
				$to_rgt = $to_lft + $width - 1;
				$depth = $parent_node->depth + 1;
			}
			//新位置為第一個節點
			else	
			{				
				$to_lft = 1;
				$to_rgt = $to_lft + $width - 1;
				$depth = 0;
			}
		}			
		
		if($from_lft != $to_lft)
		{
			//從前往後移
			if($from_lft < $to_lft)
			{
				$to_rgt = $to_lft - 1;
				$to_lft = $to_rgt - $width + 1;
				$move_forward = true;
			}
			//從後往前移
			elseif($from_lft > $to_lft)
			{
				$move_forward = false;				
			}
			$offset = $to_rgt - $from_rgt;
			//echo $from_lft.' '.$from_rgt.' '.$to_lft.' '.$to_rgt.'<br/>';
			
			//寫入數據庫
			$this->db->set('rgt', '-rgt', FALSE);
			$this->db->set('lft', '-lft', FALSE);
			$this->db->where('lft BETWEEN '.(int)$from_lft.' AND '.(int)$from_rgt);
			$this->db->update('category');
			//echo $this->db->last_query().'<br/>';
			
			$this->db->set('rgt', 'rgt + '.(int)($move_forward ? -$width : $width), FALSE);
			$this->db->where('rgt BETWEEN '.(int)($move_forward ? $from_lft : $to_lft).' AND '.(int)($move_forward ? $to_rgt: $from_rgt));
			$this->db->update('category');
			//echo $this->db->last_query().'<br/>';

			$this->db->set('lft', 'lft + '.(int)($move_forward ? -$width : $width), FALSE);
			$this->db->where('lft BETWEEN '.(int)($move_forward ? $from_lft : $to_lft).' AND '.(int)($move_forward ? $to_rgt: $from_rgt));
			$this->db->update('category');
			//echo $this->db->last_query().'<br/>';
			
			$this->db->set('rgt', '-rgt + '.$offset, FALSE);
			$this->db->set('lft', '-lft + '.$offset, FALSE);
			$this->db->where('lft < ', 0);
			$this->db->update('category');
			//echo $this->db->last_query().'<br/>';
			
			$basic_data['lft'] = $to_lft;
			$basic_data['rgt'] = $to_rgt;
			$basic_data['depth'] = $depth;
		}
		
		
		
		$this->db->where('category_id', (int)$category_id);
		$this->db->update('category', $basic_data);
		
		$this->db->query("UNLOCK TABLES");		
		
		if (isset($data['image'])) {
			$image_data = array(
				"image" => $data['image'],
			);
			
			$this->db->where('category_id', (int)$category_id);
			$this->db->update('category', $image_data);
			
			
		}
			
		$this->db->where('category_id', (int)$category_id);
		$this->db->delete('category_description');
		

		foreach ($data['category_description'] as $language_id => $value) {
			
			$desc_data = array(
				'category_id'=>(int)$category_id,
				'language_id'=>(int)$language_id,
				'name' => $value['name'],
				'meta_keyword'=>$value['meta_keyword'],
				'meta_description'=>$value['meta_description'],
				'description'=>$value['description'],
					
			);
			
			$this->db->insert('category_description', $desc_data);
			
			
		}	
			
		$this->db->where('category_id', (int)$category_id);
		$this->db->delete('category_to_store');
		
		
		if (isset($data['category_store'])) {		
			foreach ($data['category_store'] as $store_id) {
				$category_store_data = array(
					"category_id" => (int)$category_id,
					"store_id" => (int)$store_id,
				);
				
				$this->db->insert('category_to_store', $category_store_data);
				
			}
		}
		
		$this->db->where('category_id', (int)$category_id);
		$this->db->delete('category_to_layout');
		
		

		if (isset($data['category_layout'])) {
			foreach ($data['category_layout'] as $store_id => $layout) {
				
				if ($layout['layout_id']) {
					$layout_data = array(
						"category_id" => (int)$category_id,
						"store_id" => (int)$store_id,
						"layout_id" => (int)$layout['layout_id']
					);
					$this->db->insert('category_to_layout', $layout_data);
					
				}
			}
		}
		
		$this->db->where('query', 'category_id='.(int)$category_id);
		$this->db->delete('url_alias');
						
		
		
		if ($data['keyword']) {
			$keyword_data = array(
				"query" => 'category_id=' . (int)$category_id ,
				"keyword" => $data['keyword'],
			);
				
			$this->db->insert('url_alias', $keyword_data);
			
		}
						
				
		$this->db->trans_complete();	
		
	}
	
	function deleteCategory($category_id) {
		
		$success=false;
		
		
		$this->db->trans_start();
		
		$this->db->query("LOCK TABLE " . $this->db->dbprefix . "category WRITE, " . $this->db->dbprefix . "category_description WRITE, " . $this->db->dbprefix . "category_to_store WRITE, " . $this->db->dbprefix . "category_to_layout WRITE, " . $this->db->dbprefix . "url_alias WRITE");
						
		$this->db->from('category');
		$this->db->where('category_id', (int)$category_id);
		$query = $this->db->get();	
		if($query->num_rows() > 0)
		{
			$parent_node = $query->row();
			$lft = $parent_node->lft;
			$rgt = $parent_node->rgt;
			$width = $rgt - $lft + 1;
			
			$this->db->from('category');
			$this->db->where('lft BETWEEN '.$lft.' AND '.$rgt);
			$query = $this->db->get();	
	
			foreach ($query->result() as $result)
			{			
				if($this->db->delete('category', array('category_id' => (int)$result->category_id)))
				{				
					if($this->db->delete('category_description', array('category_id' => (int)$result->category_id)))
					{
						if($this->db->delete('category_to_store', array('category_id' => (int)$result->category_id)))
						{
							if($this->db->delete('category_to_layout', array('category_id' => (int)$result->category_id)))
							{
								$success = $this->db->delete('url_alias', array('query' => 'category_id=' . (int)$result->category_id));	
							}
						}
					}
				}
			}
			
			
			//调整lft和rgt
			$this->db->set('rgt', 'rgt - '.$width, FALSE);
			$this->db->where('rgt > ', (int)$rgt);
			$this->db->update('category');
			$this->db->set('lft', 'lft - '.$width, FALSE);
			$this->db->where('lft >', (int)$rgt);
			$this->db->update('category');		
		}
		
		$this->db->query("UNLOCK TABLES;");
			
		$this->db->trans_complete();
		
		/*
		if($success)
		{
			//对该分类的子分类进一步删除
			$this->db->from('category');
			$this->db->where('parent_id', (int)$category_id);
			$subcate = $this->db->get();
			
			
	
			foreach ($subcate->result() as $sub) {
				$this->deleteCategory($sub->category_id);
			}
		}
		*/
		return true;
		
		
		
	} 
	
	

		
	public function getTotalCategories() {
      	$query = $this->db->query("SELECT COUNT(*) AS total FROM " . $this->db->dbprefix . "category");
		
		return $query->row('total');
	}	
		
	public function getTotalCategoriesByImageId($image_id) {
      	$query = $this->db->query("SELECT COUNT(*) AS total FROM " . $this->db->dbprefix . "category WHERE image_id = '" . (int)$image_id . "'");
		
		return $query->row('total');
	}

	public function getTotalCategoriesByLayoutId($layout_id) {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM " . $this->db->dbprefix . "category_to_layout WHERE layout_id = '" . (int)$layout_id . "'");

		return $query->row('total');
	}		
	


}
?>
